#!/usr/bin/python
# -*- coding: UTF-8 -*-

# 用于2020年报地区处重点流域项目报表
#
#     1. 玉溪市-202003-附件1：云南省“双十”重大工程（九大高原湖泊流域生态保护与修复工程）项目进展情况表
#     2. 玉溪市-202003-附件2：重点流域（除九湖外）治理项目调度表
#
# 读取各县区上报的表格，在上个月报表的基础上，得到最新的报表
#
# 最后修改：2020-4-23
#


import os
import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font
from win32com.client import Dispatch


THREE_LAKE_VALUE_MAP = {
    '抚仙湖': 'C7:BD14',
    '杞麓湖': 'C16:BD21',
    '星云湖': 'C23:BD40',
}

SRC_FOLDER = 'src'


def getFont(color, size):
    return Font(u'宋体', size=size, bold=False,
                italic=False, strike=False, color=color)


text_red_10 = getFont('ff0000', 10)
text_black_10 = getFont('000000', 10)

text_red_16 = getFont('ff0000', 16)
text_black_16 = getFont('000000', 16)

text_red_12 = getFont('ff0000', 12)
text_black_12 = getFont('000000', 12)


def read_from_xlsx(path, offset=0):
    '''从 xlsx 文件读取数据'''
    wb = load_workbook(path, data_only=True)
    ws = wb.active
    data = list(ws.values)[offset:]
    res = []
    for row in data:
        res.append(row)
    return res

# 用openpyxl读取excel的load_workbook加上data_only参数，读取不到值的原因。
# 要用excel打开并保存才会读得到
# https://blog.51cto.com/antidarkness/1974684
#


def openAndSave(filename):
    xlApp = Dispatch("KET.Application")
    xlApp.Visible = False
    path = os.path.join(os.getcwd(), filename)
    xlBook = xlApp.Workbooks.Open(path)
    xlBook.Save()
    xlBook.Close()


def wanToYi(val):
    return round(int(val) / 10000, 2)


class Report():
    def __init__(self):
        self.set_folder()

    def set_folder(self):
        folder = input('输入月份，格式为 <2020-03>：')
        [year, month] = folder.split('-')
        this_month = datetime.date(int(year), int(month), 1)
        last_month = datetime.date(int(year), int(month) - 1, 1)
        self.this_month = f'{this_month.year}年{this_month.month}月'
        self.last_month = f'{last_month.year}年{last_month.month}月'
        print(self.last_month, '->', self.this_month, )

        self.folder = f'{SRC_FOLDER}/{folder}'
        self.files = os.listdir(f'{SRC_FOLDER}/{folder}')


class LakeReport(Report):
    def __init__(self):
        super().__init__()
        self.src_file = f'玉溪市-{self.last_month}-附件1：云南省“双十”重大工程（九大高原湖泊流域生态保护与修复工程）项目进展情况表.xlsx'
        self.dst_file = f'玉溪市-{self.this_month}-附件1：云南省“双十”重大工程（九大高原湖泊流域生态保护与修复工程）项目进展情况表.xlsx'

    def sumary(self, file):
        wb = load_workbook(file, data_only=True)
        ws = wb.active

        total = wanToYi(ws['S6'].value)
        total2019 = wanToYi(ws['T6'].value)
        current2020 = wanToYi(ws['W6'].value)
        currentMonth2020 = wanToYi(ws['V6'].value)
        recive = wanToYi(ws['Z6'].value)
        sumaryStr = f'总投资{total}亿元，截止2019年完成投资{total2019}亿元，{self.this_month}完成投资{currentMonth2020}亿元，2020年累计完成投资{current2020}亿元，总到位资金{recive}亿元。'
        print('汇总', sumaryStr)

    def generate(self):
        src_file = self.src_file if os.path.exists(
            self.src_file) else 'lake_template.xlsx'
        wb = load_workbook(src_file)
        ws = wb.active

        for f in self.files:
            data = read_from_xlsx(f'{self.folder}/{f}', offset=6)
            name = str(data[0][0]).replace('\n', '')
            if not name in THREE_LAKE_VALUE_MAP:
                continue
            print('处理', name)
            self.sumary(f'{self.folder}/{f}')

            mod_data = ws[THREE_LAKE_VALUE_MAP.get(name)]
            offset = 2
            for rowIndex, r in enumerate(mod_data):
                if r[1].value == data[rowIndex][3]:
                    for colIndex in range(19, 54):
                        newVal = data[rowIndex][colIndex + offset]
                        if r[colIndex].value != newVal:
                            print(
                                f'修改 {r[colIndex].coordinate}, {r[colIndex].value} -> {newVal}')
                            r[colIndex].value = newVal
                            r[colIndex].font = text_red_16
                        else:
                            r[colIndex].font = text_black_16

        ws['B2'].value = f'云南省“双十”重大工程（九大高原湖泊流域生态保护与修复工程）项目进展情况表（{self.this_month}）'
        wb.save(self.dst_file)
        print('完成')


class OtherReport(Report):
    def __init__(self):
        super().__init__()
        self.src_file = f'玉溪市-{self.last_month}-附件2：重点流域（除九湖外）治理项目调度表.xlsx'
        self.dst_file = f'玉溪市-{self.this_month}-附件2：重点流域（除九湖外）治理项目调度表.xlsx'

    def sumary(self):
        wb = load_workbook(self.dst_file, data_only=True)
        ws = wb.active

        total = wanToYi(ws['C4'].value)
        total2019 = wanToYi(ws['K4'].value)
        current2020 = wanToYi(ws['N4'].value)
        sumaryStr = f'总投资{total}亿元。截止2019年完成投资{total2019}亿元，2020年完成投资{current2020}亿元。'
        print('汇总', sumaryStr)

    def generate(self):
        src_file = self.src_file if os.path.exists(
            self.src_file) else 'other_template.xlsx'
        wb = load_workbook(src_file)
        ws = wb.active

        for f in self.files:
            data = read_from_xlsx(f'{self.folder}/{f}', offset=4)
            if not type(data[0][0]) == int:
                continue
            print('处理', f)

            mod_data = ws['A5:T8']

            for i, r in enumerate(mod_data):
                for j in data:
                    if r[1].value == j[1]:
                        for k in range(2, 19):
                            if r[k].value != j[k]:
                                newVal = j[k]
                                print(
                                    f'修改 {r[k].coordinate}, {r[k].value} -> {newVal}')
                                r[k].value = newVal
                                r[k].font = text_red_10
                            else:
                                r[k].font = text_black_10

        ws['A1'].value = f'重点流域(除九湖外)治理项目投资完成情况表（{self.this_month}）'
        wb.save(self.dst_file)
        openAndSave(self.dst_file)
        self.sumary()

        print('完成')


class ZhuanZhaiReport(Report):
    def __init__(self):
        super().__init__()
        # 玉溪市-2020年8月-云南省2020年地方政府专项债券（流域水环境治理）项目发行安排情况表
        self.src_file = f'玉溪市-{self.last_month}-云南省2020年地方政府专项债券（流域水环境治理）项目发行安排情况表.xlsx'
        self.dst_file = f'玉溪市-{self.this_month}-云南省2020年地方政府专项债券（流域水环境治理）项目发行安排情况表.xlsx'

    def generate(self):
        src_file = self.src_file if os.path.exists(
            self.src_file) else 'zhuanzhai_template.xlsx'
        wb = load_workbook(src_file)
        ws = wb.active

        for f in self.files:
            data = read_from_xlsx(f'{self.folder}/{f}', offset=5)
            if not data[0][2] == '2020年提前发行的1万亿':
                continue
            print('处理', f)

            mod_data = ws['A6:T6']

            for rowIndex, mod_row in enumerate(mod_data):
                for data_row in data:
                    if mod_row[4].value == data_row[4]:
                        for k in range(5, 20): # range 不包含最后一个数
                            if mod_row[k].value != data_row[k]:
                                newVal = data_row[k]
                                print(
                                    f'修改 {mod_row[k].coordinate}, {mod_row[k].value} -> {newVal}')
                                mod_row[k].value = newVal
                                mod_row[k].font = text_red_12
                            else:
                                mod_row[k].font = text_black_12

        # 云南省2020年地方政府专项债券（流域水环境治理）项目发行安排情况表(8月)
        ws['A1'].value = f'云南省2020年地方政府专项债券（流域水环境治理）项目发行安排情况表（{self.this_month}）'
        wb.save(self.dst_file)
        openAndSave(self.dst_file)
        print('完成')


if __name__ == "__main__":
    report_type = input('选择模板, 1 是九湖, 2 是除九湖外, 3 是专债：')
    if report_type == '1':
        lake_report = LakeReport()
        lake_report.generate()
    elif report_type == '2':
        other_report = OtherReport()
        other_report.generate()
    elif report_type == '3':
        zhuanzhai_report = ZhuanZhaiReport()
        zhuanzhai_report.generate()
    else:
        print(f'没有模板 {report_type}')
